/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bean;

import Data.CalonSiswa;
import Data.Sekolah;
import Data.UserSekolah;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Ronny
 */
public class ProsesBean {
    //koneksi jdbc
    private String jdbcURL = "jdbc:oracle:thin:@172.23.9.185:1521:orcl";
    private String user = "MHS105314088";
    private String password = "MHS105314088";
    private Connection connection = null;

//    private String jdbcURL = "jdbc:oracle:thin:@localhost:1521:xe";
//    private String user = "ronny";
//    private String password = "oracle";
//    private Connection connection = null;
    Connection conn;

 
//    Connection conn;
    public ProsesBean() {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            connection = DriverManager.getConnection(jdbcURL, user, password);
        } catch (Exception ex) {
            System.out.println("message: " + ex.getMessage());
        }
    }

    public boolean isConnected() {
        if (connection != null) {
            return true;
        } else {
            return false;
        }
    }

    public boolean getClosed() {
        if (isConnected()) {
            try {
                connection.close();
                connection = null;
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return true;
        } else {
            return false;
        }
    }

    public String getJdbcURL() {
        return jdbcURL;
    }

    public void setJdbcURL(String jdbcURL) {
        this.jdbcURL = jdbcURL;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Connection getConnection() {
        return connection;
    }

    public void setConn(Connection connection) {
        this.connection = connection;
    }

    //Proses Login
    public ResultSet login(String username, String password) throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select username,password from usersekolah where username='" + username + "'and password= '" + password + "'";
        System.out.println(username);
        System.out.println(password);
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }

    //Proses melihat kuota sekolah
    public ResultSet kuotaSekolah() throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select namasekolah, kuota from sekolah";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }

    //Proses melihat daftar diterima
    public ResultSet siswaDiterima() throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select nopendaftaran,nama,sekolahasal,sekolahditerima from calonsiswa where statusterima='ya' ";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }

    //Proses pendaftaran
    public void Pendaftaran(CalonSiswa cln) throws SQLException {
        PreparedStatement stmt = null;
        try {
            connection.setAutoCommit(false);
            String query = "insert into calonsiswa values (?,?,?,?,?,?,?,?,'kosong','kosong')";
            stmt = connection.prepareStatement(query);
            stmt.setInt(1, cln.getNoPendaftaran());
            stmt.setInt(2, cln.getNoUjian());
            stmt.setString(3, cln.getNama());
            stmt.setString(4, cln.getTanggal_lahir());
            stmt.setString(5, cln.getAsalSekolah());
            stmt.setInt(6, cln.getNilai());
            stmt.setString(7, cln.getPilihan_1());
            stmt.setString(8, cln.getPilihan_2());

            stmt.executeUpdate();
            connection.commit();
        } catch (SQLException se) {
            connection.rollback();
            throw se;
        } finally {
            try {
                connection.setAutoCommit(true);
                if (stmt != null) {
                    stmt.close();
                }
            } catch (Exception e) {
                try {
                    throw e;
                } catch (Exception ex) {
                    Logger.getLogger(ProsesBean.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
    }

    //Proses melihat daftar diterima
    public ResultSet profilSekolah(String username) throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select * from sekolah where username='" + username + "' ";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }

    //Proses melihat daftar yang diterima oleh sekolah
    public ResultSet daftarDiterimaSekolah(String username) throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select nopendaftaran, nama, nilaitotal from calonsiswa where sekolahditerima = (select namasekolah from sekolah where username='" + username + "') ";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
    }

    //Proses Update Profil sekolah
    public void UpdateSekolah(Sekolah update, String username) throws SQLException {
        PreparedStatement statUP = null;
        try {
            connection.setAutoCommit(false);
            String sql = "Update  sekolah set namasekolah=? , alamat=? , email=? , kuota=? where username='" + username + "' ";
            statUP = connection.prepareStatement(sql);
            statUP.setString(1, update.getNamaSekolah());
            statUP.setString(2, update.getAlamat());
            statUP.setString(3, update.getEmail());
            statUP.setString(4, update.getQuota());

            statUP.executeUpdate();
            connection.commit();
        } catch (SQLException x) {
            connection.rollback();
            throw x;

        }
    }

    //Proses melihat daftar yang mendaftar ke sekolah
    public ResultSet daftarPendaftar(String username) throws SQLException {

        Statement statement = connection.createStatement();
        String query = "select nopendaftaran, nama, nilaitotal, sekolahasal from calonsiswa "
                + "where sekolahditerima='kosong' and (statusterima='kosong' or statusterima='tidak') and (pilihan1 = (select namasekolah from sekolah where username='" + username + "') "
                + "or ( pilihan2 = (select namasekolah from sekolah where username='" + username + "')and statusterima='tidak')) order by nilaitotal";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;

    }

    //Proses seleksi diterima
    public void seleksiDiterima(int nopend, String username) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            String query = "update calonsiswa set statusterima='ya' ,"
                    + "sekolahditerima = (select namasekolah from sekolah where username='" + username + "')"
                    + "where nopendaftaran=" + nopend + " ";
            int ok = statement.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(ProsesBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    //Proses seleksi ditolak
    public void seleksiDitolak(int nopend) throws SQLException {
        try {
            Statement statement = connection.createStatement();
            String query = "update calonsiswa set statusterima='tidak'"
                    + "where nopendaftaran=" + nopend + " ";
            int ok = statement.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(ProsesBean.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    //get data siswa diterima
    public ResultSet getDataVerifikasi(int nopend) throws SQLException {

        Statement statement = connection.createStatement();
        String query = "select nopendaftaran, nama, nilaitotal, sekolahasal,sekolahditerima from calonsiswa "
                + "where nopendaftaran=" + nopend + "";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;

    }
//proses mencari nopendaftaran max terbesar
    public ResultSet cariNoPendafMAX() throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select max(nopendaftaran) from calonsiswa";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
                
    }
    // cek apakah pernah di pakai
     public ResultSet noPendf(int nopendf) throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select nopendaftaran from calonsiswa where nopendaftaran="+nopendf+" order by nopendaftaran";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
                
    }
     
      public ResultSet noPendfVerifi(int noujian) throws SQLException {
        Statement statement = connection.createStatement();
        String query = "select nopendaftaran from calonsiswa where noujian="+noujian+" ";
        System.out.println(query);
        ResultSet rest = statement.executeQuery(query);
        return rest;
                
    }
    //Proses mengecek no ujian    
    public ResultSet cekNoUjian(int noujian) throws SQLException{
            Statement statement = connection.createStatement();
            String query = "select noujian from calonsiswa where noujian="+ noujian +"";
            System.out.println(query);
            ResultSet rest=statement.executeQuery(query);
            return rest;
    }
    //proses search
    public ResultSet Seacrh(int noujian) throws SQLException{
            Statement statement = connection.createStatement();
            String query = "select nopendaftaran,nama,statusterima from calonsiswa where nopendaftaran="+noujian+"";
            System.out.println(query);
            ResultSet rest=statement.executeQuery(query);
            return rest;
    }
}
